package notRelevantAnymore;

import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.Map;

import db.DB_SQL;
import db.SQL_Schema;
import parsing.City;
import parsing.Continent;
import parsing.Country;
import parsing.GeoParser;
import parsing.ParsingCitiesToTextB;
/*
public class DB_update {

/*	static DB_SQL mySQL = new DB_SQL();

	public static void main(String[] args) {
		assert (args.length == 2);
		if (mySQL.connect() == 0)
			System.out.println("Failed To Connect");
		else
			System.out.println("Succed To Connect");

		updateGeoTables(args[0], args[1]);
	}

	public static void updateGeoTables(String continentsCountriesFile,

	String citiesFile) {
		// the order is importent because there is a restrict policy on all the
		// tables
		LinkedList<Continent> continents = null;
		Map<String, Country> countries = null;
		parsing.GeoParser
				.parserContinentCountryCity(continentsCountriesFile, 1);
		continents = parsing.GeoParser.continents;
		countries = parsing.GeoParser.countries;
		/*
		 * if ((updateContinentsTable(continents)) == 0) {
		 * System.out.println("Error"); // TODO transfer to the gui } if
		 * ((updateCounntriesTable(countries)) == 0) {
		 * System.out.println("Error"); // TODO transfer to the GUI }
		 */
	/*	if ((updateCitiesTable(citiesFile)) == 0) {
			System.out.println("Error");
			// TODO transfer to the GUI }

		}
	}

	public static int updateContinentsTable(LinkedList<Continent> continents) {
		// read from the file into JAVA Data structure
		Continent tempContinent = null;
		Continent continentFromFile = null;
		// check if continent exists in the table
		Iterator<Continent> continentIter = continents.iterator();
		// iterating over the list of continents
		while (continentIter.hasNext()) {
			continentFromFile = continentIter.next();
			// tempContinent the result from the continents table
			tempContinent = mySQL.getContinent(continentFromFile
					.getContinentId());
			// if continent does not exists in the database
			if (tempContinent.getContinentId() == (-1)) {
				if (mySQL.addNewContinent(continentFromFile) == 0) {
					// TODO error to the user in the gui
					System.out.println("Error adding new continents");
					return 0;
				}
			} else
			// else check if it is the same continent
			if (!(continentFromFile.getContinentName().equals(tempContinent
					.getContinentName()))) {
				// upate continent name
				if ((mySQL.modifyContinent(continentFromFile,
						continentFromFile.getContinentName()) == 0)) {
					// TODO error message to the GUI;
					System.out.println("Error updating continent name");
					return 0;
				}
			} else {
				continue;
			}

		}
		return 1;
	}

	public static int updateCounntriesTable(Map<String, Country> countries) {
		// tempCountry is the result from the countries table
		Country tempCountry = null;
		// countryFromFile is the country from the file
		Country countryFromFile = null;
		for (Map.Entry<String, Country> entry : countries.entrySet()) {
			countryFromFile = entry.getValue();
			// countryID is primery key must be unique
			tempCountry = mySQL.getCountry(countryFromFile.getCountryId());
			// if country does not exists in the database
			if (tempCountry.getContinentId() == (-1)) {
				if (mySQL.addNewCountry(countryFromFile) == 0) {
					// TODO error to the user in the GUI
					System.out.println("Error adding new country");
					return 0;
					// check if the name is equal
					// assumption - continents and countries are updated by the
					// same file
					// continents table was updated first there is no
					// continentID missing
					// user can not change continent_id from a country

				} else if (!(tempCountry.equals(countryFromFile))) {
					// check if countryName is different and update
					if (!(tempCountry.getCountryName().equals(countryFromFile
							.getCountryName()))) {
						if ((mySQL.modifyCountry(countryFromFile,
								countryFromFile.getCountryName()) == 0)) {
							// TODO error message to the GUI;
							System.out.println("Error updating country");
							return 0;
						}
					} else {
						continue;
					}

				}
			}
		}

		return 1;

	}

	public static int updateCitiesTable(String citiesFile) {

		City cityFromFile = null;
		City tempCity = null;
		int cityFromFileId = 0;
		Map<String, City> cities = null;
		parsing.GeoParser.parserContinentCountryCity(citiesFile, 0);
		cities = GeoParser.cities;
		for (Map.Entry<String, City> entry : cities.entrySet()) {
			cityFromFile = entry.getValue();
			// search for the city in citiesSearch
			cityFromFileId = mySQL.getCityIDFromCitiesSearch(
					cityFromFile.getCityName(), cityFromFile.getCountryId());
			// the city was not found
			if (cityFromFileId == -1) {
				mySQL.addNewCity(cityFromFile);
				//int newCityId = mySQL.findMaxCityId();
				//if (newCityId == 0) {
					// TODO error GUI
					//System.out.println("Error updating cities table");
					//return 0;
			//	}
				//mySQL.addNewCityToCitySearch(cityFromFile, newCityId);// add to
																		// citiesSearch
			} else {
				// city exists
				tempCity = mySQL.getCity(cityFromFileId);
				// city_name and cityID are the same ( already checked in
				// citiesSearch*/
	/*			if (cityFromFile.getLatitude() != tempCity.getLatitude()) {
					if ((mySQL.modifyCityLat(cityFromFile.getLatitude(),
							cityFromFileId) == 0)) {
						// TODO error GUI
						System.out.println("Error updating cities table");
						return 0;
					}
				}
				if (cityFromFile.getLongtitude() != tempCity.getLongtitude()) {
					if (mySQL.modifyCityLong(cityFromFile.getLongtitude(),
							cityFromFileId) == 0) {
						// TODO error GUI
						System.out.println("Error updating cities table");
						return 0;
					}
				}

			}

		}
		return 1;
	}

	 
}

public int updateCitiesTable(String citiesFile) {

	
	City cityFromFile = null;
	City tempCity = null;
	int cityFromFileId = 0;
	int numOfFiles;
	int numOfCities=0;
	ParsingCitiesToTextB.CityParser("citiesTest", citiesFile);
	numOfFiles = ParsingCitiesToTextB.numOfOutputsFile;
	for (int j = 1; j < numOfFiles; j++) {
		if (GeoParser.cities!=null){
		GeoParser.cities.clear();
		}
		try {
			this.getConn().setAutoCommit(false);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
	
			e.printStackTrace();
			return 0;
		}
		parsing.GeoParser.parserContinentCountryCity("citiesTest" + j, 0);
		// add cities to the map
		for (Map.Entry<String, City> entry : GeoParser.cities.entrySet()) {
			cityFromFile = entry.getValue();
			numOfCities++;
			this.InsertIgnore(cityFromFile);
			// search for the city in citiesSearch
			/*
			cityFromFileId = this
					.getCityIDFromCitiesSearch(cityFromFile.getCityName(),
							cityFromFile.getCountryId());
			// the city was not found
			if (cityFromFileId == -1) {
				this.addNewCity(cityFromFile);
				//int newCityId = this.findMaxCityId();
				//if (newCityId == 0) {
					// TODO error GUI
				//	System.out.println("Error updating cities table");
				//	return 0;
				//}
				// this.addNewCityToCitySearch(cityFromFile, newCityId);//
				// add to
				// citiesSearch
			} else {
				// city exists
				tempCity = this.getCity(cityFromFileId);
				// city_name and cityID are the same ( already checked in
				*/// citiesSearch*/
				/*if (Math.abs(cityFromFile.getLatitude()
						- tempCity.getLatitude()) > 0.01) {
					if ((this.modifyCityLat(cityFromFile.getLatitude(),
							cityFromFileId) == 0)) {
						// TODO error GUI
						System.out.println("Error updating cities table");
						return 0;
					}
				}
				if (Math.abs(cityFromFile.getLongtitude()
						- tempCity.getLongtitude()) > 0.01) {
					if (this.modifyCityLong(cityFromFile.getLongtitude(),
							cityFromFileId) == 0) {
						// TODO error GUI
						System.out.println("Error updating cities table");
						return 0;
					}
				}*/
/*			if ((numOfCities% 1000000)==0){
				try {
					stmt.executeBatch();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					return 0;
				}
				

			}
		}
//try {
//	this.getConn().commit();
//} catch (SQLException e) {
//	// TODO Auto-generated catch block
//	e.printStackTrace();
//	return 0;
//}
/*	}
/*	return 1;
}
// check if city is in the DB
// pay attention by searching for cities we need to search for cityNAme ( need
// to create new table from the other kind) and then by country_id
// by searching by name we will get a group of city_id
// every one of them need to be checked by its country_id
/*
 * first read from file- function second iterate over all the cities in map
 * search by name in citiesSearcc functions: the map is built from key
 * city_country problem!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! function that searches in
 * citiesSearch by name returns rs with tuples function that iterates all over
 * the rs and numbers function that searches by id in citiesTest and returns a
 * city
 * 
 * 
 * 
 * in the previous functions we checked if
 * 
 * when we insert into table countryID and city name are unique so it doesnt
 * enter 2 cities with the same name in the same country if we add another index
 * to the table citiesSearch we will get one result back (city_id ) if exists
 * then we can call the city from citiesTest and check if it is the same : by
 * equals ( (city_name, countryID is equal need to check long lat ) people can
 * change long lat because we assume there is only one city with the same name
 * within a country we need to update
 * 
 * 
 * 
 * 
 * functions to write: 1.read from file into MAP<String,City> V 2.modify long V
 * 3.modify lat V create citiesSearch V 4.search in citiesSerach by NAME and by
 * country_id V 6. search by ID V 5.create City V if city doesnot exsists add it
 * 7. add to the citiesSearch 6.check check if
 */
/* the first try of update cities table creating a table with city_id, city_name and countryID 
 * the table was myIASM table and no innoDB
 */
/*public int addNewCityToCitySearch(City newCity, int city_id) {
	try {
		String table_name = SQL_Schema.CitiesSearch.TABLE_NAME;
		String city_name_str = SQL_Schema.CitiesSearch.CityName;
		String country_id_str = SQL_Schema.CitiesSearch.CountryID;
		String city_id_str = SQL_Schema.CitiesSearch.CityID;

		String insertCommand = "INSERT INTO " + table_name + " ("
				+ city_id_str + "," + city_name_str + "," + country_id_str
				+ ")";

		pstm = conn.prepareStatement(insertCommand + " VALUES (?, ?, ? );");
		pstm.setInt(1, city_id);
		pstm.setString(2, newCity.getCityName());
		pstm.setInt(3, newCity.getCountryId());

		pstm.addBatch();
		pstm.executeBatch();

	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		return 0;
	}

	return 1;
}

@Override
public int modifyCitySearch(int cityID, String newName) {
	try {
		String table_name = SQL_Schema.CitiesSearch.TABLE_NAME;
		String city_name_str = SQL_Schema.CitiesSearch.CityName;
		String city_id_str = SQL_Schema.CitiesSearch.CityID;

		String UPDATE = "Update " + table_name;
		String SET = " SET " + city_name_str + "= ?";
		String WHERE = " WHERE " + city_id_str + "= ?";

		String insertCommand = UPDATE + SET + WHERE;

		pstm = conn.prepareStatement(insertCommand + ";");
		pstm.setString(1, newName);
		pstm.setInt(2, cityID);

		pstm.addBatch();
		pstm.executeBatch();

	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
		return 0;
	}

	return 1;

}
//citySearch

	public int getCityIDFromCitiesSearch(String city_name, int country_id) {
		int cityId;
		String cityId_str = null;
		try {
			String query = "SELECT * " + " FROM "
					+ SQL_Schema.CitiesSearch.TABLE_NAME + " WHERE "
					+ SQL_Schema.CitiesSearch.CityName + "  =  " + "'"
					+ city_name + "'" + " AND "
					+ SQL_Schema.CitiesSearch.CountryID + " = " + country_id
					+ ";";
			rs = stmt.executeQuery(query);
			if (rs.next()) {
				cityId_str = rs.getString(SQL_Schema.CitiesSearch.CityID);
			} else {
				return -1;
			}
			cityId = Integer.parseInt(cityId_str);
		} catch (SQLException e) {
			// TODO to notify the user via GUI
			e.printStackTrace();
			return -1;
		}

		return cityId;

	}
	

	public int findMaxCityId() {
		int maxId;
		String maxId_str = null;
		try {

			String query = "select max(city_id) from "
					+ SQL_Schema.Cities.TABLE_NAME + ";";

			rs = stmt.executeQuery(query);
			if (rs.next()) {
				maxId_str = rs.getString("max("
						+ SQL_Schema.CitiesSearch.CityID + ")");
			}
			maxId = Integer.parseInt(maxId_str);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}

		return maxId;
	}
*/